if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RptCSCRActivityMCAFeeRequiredReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[RptCSCRActivityMCAFeeRequiredReport]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =======================================================================
-- Procedure Name: dbo.RptCSCRActivityMCAFeeRequiredReport
--
-- Author:      Arthur A. Bianchini III
--
-- Create date: 4/18/2008
--
-- Description: Source of Report, "CSCR Activity Report"
--                  In Insight DB Reporting Services
--
--Purpose
--
-- Parameters:  
--     @StartDate                           
--     @EndDate        
--	modified 9/11/08  aab  update code to select the same data as MCAFeeActivity report        
-- 6/1/2009  aab -- change code to codeid where pt.arr_name = 'PROP_TYPE'           
-- =======================================================================

CREATE PROCEDURE [dbo].[RptCSCRActivityMCAFeeRequiredReport]
(
	@CSCRExecutedStartDate								datetime,
	@CSCRExecutedEndDate									datetime,
	@DomainUserId            INT
)
 AS

BEGIN

	CREATE TABLE #TempCSCRActivityMCAFeeRequired

	(ProposalNumber									varchar(30) NULL, 
	 MCAFeeRequired									varchar(70) NULL,
	 CSCRExecutedStartDate						datetime NULL,
	 CSCRExecutedEndDate						datetime NULL,
	 DomainUserId										INT NULL
	)

INSERT INTO #TempCSCRActivityMCAFeeRequired

SELECT DISTINCT
p.inst_no as ProposalNumber,
CASE u5.p_sin_32 
	WHEN '3-None/0' THEN 'No'
	WHEN '1-Standard/1500' THEN 'Yes'
	WHEN '2-Device/2000' THEN  'Yes'
END as MCAFeeRequired,
@CSCRExecutedStartDate,
@CSCRExecutedEndDate,
@DomainUserId
FROM proposal p
JOIN	PropPds pd
	ON	p.prop_no = pd.prop_no
	AND	p.inst_code = pd.inst_code
	AND pd.first_pd = 1
JOIN	 prop_stat feaa --aab 7/24/2008  select date change to FEAA
	on feaa.prop_no = p.prop_no 
	and feaa.inst_code = p.inst_code 
	and feaa.prop_stat = (select codeid 
									from codetab 
									where arr_name = 'prop_stat' 
									and inst_code = 'partners'
									and code_desc = 'Fully Executed/Awarded - Activated')
	AND	feaa.effectivedt = (SELECT MAX(feaa1.effectivedt)
									FROM	prop_stat feaa1
									WHERE	feaa.prop_no = feaa1.prop_no
										AND	feaa.inst_code = feaa1.inst_code
										AND	feaa.prop_stat = feaa1.prop_stat
									AND	(feaa.effectivedt BETWEEN @CSCRExecutedStartDate AND DATEADD(day,1,@CSCRExecutedEndDate)))
JOIN codetab pt
	on pt.codeid = p.prop_type 
	AND	pt.inst_code = p.inst_code
	and pt.arr_name = 'PROP_TYPE' 
	and pt.code_desc = 'Master'
JOIN Unit un 
	ON		p.inst_code = un.inst_code
	AND		p.unit_code = un.unit_code
JOIN Unit as inst
          ON    inst.inst_code = un.inst_code
          AND  left(un.deptid,4) = inst.deptID
          AND  len(inst.deptID) = 4
		 -- AND inst.int_unit_code in ('1200','2200','1400','FH','NWH','DFCI','DFPCC')
JOIN prop_u u2--get activity type
	ON p.inst_code = u2.inst_code 
	AND p.prop_no = u2.prop_no
	AND	u2.p_sin_14 = 'Clinical Research (C)'
JOIN prop_u u3-- get subactivity type
	ON p.inst_code = u3.inst_code 
	AND p.prop_no = u3.prop_no
	AND	u3.p_sin_15 in  ('Trial - Jointly Initiated (C)','Trial - Other (C)','Trial - Company Initiated (C)','Trial - PI Initiated (C)')
LEFT JOIN		CodeTab f2
	ON	p.prop_stat = f2.codeID
	AND	p.inst_code = f2.inst_code
	AND f2.arr_name = 'PROP_STAT'
 JOIN		prop_u u5--mca fee
	ON	p.inst_code = u5.inst_code
	AND	p.prop_no = u5.prop_no
	AND	u5.p_sin_32 is not null and u5.p_sin_32 <> ''
JOIN INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements (@DomainUserId) sec --updated on 1/28/09
	ON p.prop_no = sec.InfoEdPropNumber
WHERE len(p.inst_no) = 11
	AND		p.system = 'pt'
	AND		p.inst_code = 'partners'

CREATE TABLE #TempMCAFeeRequired

(MCAFeeRequired							varchar(70) NULL,
 ProposalCount									INT Null
)

INSERT INTO	#TempMCAFeeRequired

(MCAFeeRequired)

SELECT DISTINCT
CASE p_sin_32 
	WHEN '3-None/0' THEN 'No'
	WHEN '1-Standard/1500' THEN  'Yes'
	WHEN '2-Device/2000' THEN  'Yes'
END as MCAFeeRequired
FROM prop_u
WHERE p_sin_32 is not null and p_sin_32 <> ''
ORDER BY MCAFeeRequired 

SELECT DISTINCT
COUNT(*) as ProposalCount,
MCAFeeRequired
INTO #TempMCAFeeCount
FROM	#TempCSCRActivityMCAFeeRequired
GROUP BY MCAFeeRequired
ORDER BY MCAFeeRequired

UPDATE fr
SET fr.ProposalCount = fc.ProposalCount
FROM	 #TempMCAFeeCount fc
JOIN	#TempMCAFeeRequired fr
	ON	fc.MCAFeeRequired = fr.MCAFeeRequired

--select final results for report
 
SELECT DISTINCT 
MCAFeeRequired,	
IsNull(ProposalCount,0) as ProposalCount,
@CSCRExecutedStartDate as CSCRExecutedStartDate,
@CSCRExecutedEndDate as CSCRExecutedEndDate
FROM	#TempMCAFeeRequired
ORDER BY MCAFeeRequired 

END

GO


